package com.google.code.sms.dao.schema;

import java.util.logging.Level;
import com.google.code.sms.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * Updates the database schema
 * 
 * @author scott2ware
 */
public class UpdateSchema extends Schema {
    
    private static final Logger LOG = Logger.getLogger("UpdateSchema");
    
    @Override
    public void execute(JdbcTemplate template)
    { 
        if (tableExists(template, "player"))
        {
            LOG.info("Removing data from 'player' table.");
            template.execute("delete from player");
            
            if (columnExists(template, "transcode_scheme", "player"))
            {
                LOG.info("Removing 'transcode_scheme' column in 'player' database.");
                template.execute("alter table player drop column transcode_scheme");
            }
        }
        
        if (tableExists(template, "user_settings")) {
            LOG.info("Removing 'user_settings' table from database.");
            template.execute("delete from user_settings");
            template.execute("drop table user_settings");
        }
        
        if (tableExists(template, "player_transcoding")) {
            LOG.info("Removing 'player_transcoding' table from database.");
            template.execute("delete from player_transcoding");
            template.execute("drop table player_transcoding");
        }
        
        if (tableExists(template, "transcoding")) {
            LOG.info("Removing 'transcoding' table from database.");
            template.execute("delete from transcoding");
            template.execute("drop table transcoding");
        }
        
        if (tableExists(template, "player_transcoding2")) {
            LOG.info("Removing 'player_transcoding2' table from database.");
            template.execute("delete from player_transcoding2");
            template.execute("drop table player_transcoding2");
        }
        
        if (tableExists(template, "transcoding2")) {
            LOG.info("Removing 'transcoding2' table from database.");
            template.execute("delete from transcoding2");
            template.execute("drop table transcoding2");
        }
        
        /* 
         * Deprecated
         *
        
        if (tableExists(template, "video_metadata"))
        {
            LOG.info("Adding 'rating', 'description', tagline' and 'certificate' to 'video_metadata' table.");
            
            if (!columnExists(template, "rating", "video_metadata"))
            {
                template.execute("alter table video_metadata add column rating float");
            }
            
            if (!columnExists(template, "description", "video_metadata"))
            {
                template.execute("alter table video_metadata add column description varchar");
            }
            
            if (!columnExists(template, "tagline", "video_metadata"))
            {
                template.execute("alter table video_metadata add column tagline varchar");
            }
            
            if (!columnExists(template, "certificate", "video_metadata"))
            {
                template.execute("alter table video_metadata add column certificate varchar");
            }
        }

        if (tableExists(template, "media_element")) {
            LOG.info("Removing data from 'media_element' table.");
            template.execute("delete from media_element");
        }
        
        if (tableExists(template, "video_metadata"))
        {
            LOG.info("Adding 'audio_codec', 'audio_sample_rate', video_codec' and 'audio_config' to 'video_metadata' table.");
            
            if (!columnExists(template, "video_codec", "video_metadata"))
            {
                template.execute("alter table video_metadata add column video_codec varchar");
            }
            if (!columnExists(template, "audio_codec", "video_metadata"))
            {
                template.execute("alter table video_metadata add column audio_codec varchar");
            }
            if (!columnExists(template, "audio_sample_rate", "video_metadata"))
            {
                template.execute("alter table video_metadata add column audio_sample_rate int");
            }
            if (!columnExists(template, "audio_configuration", "video_metadata"))
            {
                template.execute("alter table video_metadata add column audio_configuration varchar");
            }
        }
        
        if (tableExists(template, "audio_metadata"))
        {
            LOG.info("Adding 'codec', 'sample_rate' and 'configuration' to 'audio_metadata' table.");
            
            if (!columnExists(template, "codec", "audio_metadata"))
            {
                template.execute("alter table audio_metadata add column codec varchar");
            }
            if (!columnExists(template, "sample_rate", "audio_metadata"))
            {
                template.execute("alter table audio_metadata add column sample_rate int");
            }
            if (!columnExists(template, "configuration", "audio_metadata"))
            {
                template.execute("alter table audio_metadata add column configuration varchar");
            }
        }

        if (columnExists(template, "imported_from", "playlist")) {
            LOG.info("Removing imported_from column in playlist database.");
            template.execute("alter table playlist drop column imported_from");
        }
        
        
        if (tableExists(template, "playlist_file")) {
            LOG.info("Removing 'playlist_file' table from database.");
            template.execute("delete from playlist_file");
            template.execute("drop table playlist_file");
        }
        
        if (tableExists(template, "playlist_user")) {
            LOG.info("Removing data from 'playlist_user' table.");
            template.execute("delete from playlist_user");
        }
        
        if (tableExists(template, "playlist")) 
        {
            LOG.info("Refactoring 'playlist' table.");
            
            template.execute("delete from playlist");
            
            if(columnExists(template, "comment", "playlist"))
            {
                template.execute("alter table playlist drop column comment");
            }
            
            if(columnExists(template, "duration_seconds", "playlist"))
            {
                template.execute("alter table playlist drop column duration_seconds");
            }
            
            if(columnExists(template, "file_count", "playlist"))
            {
                template.execute("alter table playlist drop column file_count");
            }
            
            if(!columnExists(template, "duration", "playlist"))
            {
                template.execute("alter table playlist add duration int default 0 not null");
            }
            
            if(!columnExists(template, "element_count", "playlist"))
            {
                template.execute("alter table playlist add element_count int default 0 not null");
            }
        }
        
        if (columnExists(template, "selected_media_folder_id", "user_settings")) {
            LOG.info("Setting selected_media_folder_id to default.");
            template.execute("update user_settings set selected_media_folder_id = '-1'");
        }
        
        if (tableExists(template, "artist")) {
            LOG.info("Removing 'artist' table from database.");
            template.execute("delete from artist");
            template.execute("drop table artist");
        }
        
        if (tableExists(template, "album")) {
            LOG.info("Removing 'album' table from database.");
            template.execute("delete from album");
            template.execute("drop table album");
        }
        
        if (tableExists(template, "music_file_info")) {
            LOG.info("Removing 'music_file_info' table from database.");
            template.execute("delete from music_file_info");
            template.execute("drop table music_file_info");
        }
        
        if (tableExists(template, "media_file")) {
            LOG.info("Removing 'media_file' table from database.");
            template.execute("delete from media_file");
            template.execute("drop table media_file");
        }
        */
    }
    
}
